# coding: utf8


class ConnRdbms:

    def __init__(self, dbs='mysql'):
        if dbs.lower() == 'mysql':
            return
        if dbs.lower == 'oracle':
            return
        if dbs.lower() == 'sqlite':
            return

    def conn_sqlite(self):
        # (1) 创建数据库、写入数据
        print(
            "# 创建数据库、写入数据\n"
            " >>> import sqlite3\n"
            " >>> conn = sqlite3.connect('students.db')\n"
            " >>> cur = conn.cursor()\n"
            " >>> cur.execute('''CREATE TABLE score (id text, name text, math integer, art real)''')\n"
            " >>> cur.execute('''INSERT INTO score VALUES ('21101','LiQiang', 90, 80.5)''')\n"
            " >>> conn.commit()\n"
            " >>> cur.close()\n"
            " >>> comm.close()"
        )
        import sqlite3
        conn = sqlite3.connect('students.db')
        cur = conn.cursor()
        try:
            cur.execute('''CREATE TABLE score (id text, name text, math integer, art real)''')
        except sqlite3.OperationalError:
            cur.execute('''DROP TABLE score''')
            cur.execute('''CREATE TABLE score (id text, name text, math integer, art real)''')
            print("--- drop and recreated table students!")
        cur.execute("INSERT INTO score VALUES ('21101', 'LiQiang', 90, 80.8)")
        conn.commit()
        cur.close()
        conn.close()

        # (2) 读取数据库数据
        conn = sqlite3.connect('students.db')
        cur = conn.cursor()
        r = cur.execute('''select * from score''')
        print(
            "# 连接读取数据库数据\n"
            " >>> conn = sqlite3.connect('students.db')\n"
            " >>> cur = conn.cursor()\n"
            " >>> r = cur.execute('''select * from score''')\n"
            " >>> r.fetchall()\n"
            f"{r.fetchall()}\n"
            " >>> cur.close()\n"
            " >>> comm.close()\n"
        )
        cur.close()
        conn.close()

        # （3）使用参数替换方式
        conn = sqlite3.connect('students.db')
        cur = conn.cursor()
        data = [('21102', 'He', 90, 70), ('21103', 'Ma', 70, 60)]
        cur.executemany('''Insert Into score values (?, ?, ?, ?)''', data)
        cur.execute('''update score set name = ? where id = ?''', ('Su', '21101'))
        conn.commit()
        r = cur.execute('''select * from score''')
        print(
            "# 使用参数替换插入多行数据\n"
            " >>> conn = sqlite3.connect('students.db')\n"
            " >>> cur = conn.cursor()\n"
            "# 插入多条记录\n"
            " >>> data = [('21102', 'He', 90, 70), ('21103', 'Ma', 70, 60)]\n"
            " >>> cur.executemany('''Insert Into score values (?, ?, ?, ?)''', data)\n"
            "# 修改id=‘21101’记录name字段\n"
            " >>> cur.execute('''update score set name = ? where id = ?''', ('Su', '21101'))\n"
            "# 提交事务保持修改\n"
            " >>> conn.commit()\n"
            "# 再查询内容\n"
            " >>> r = cur.execute('''select * from score''')\n"
            " >>> r.fetchall()\n"
            f"{r.fetchall()}\n"
            "# 关闭游标和连接\n"
            " >>> cur.close()\n"
            " >>> comm.close()\n"
        )
        # conn.commit()
        cur.close()
        conn.close()

        return

    def query(self):
        pass

    def close(self):
        pass
